package cn.iocoder.yudao.framework.excel.core.util;

import cn.hutool.core.io.IoUtil;
import cn.idev.excel.FastExcelFactory;
import cn.idev.excel.annotation.ExcelProperty;
import cn.idev.excel.converters.longconverter.LongStringConverter;
import cn.idev.excel.read.builder.ExcelReaderBuilder;
import cn.idev.excel.read.builder.ExcelReaderSheetBuilder;
import cn.idev.excel.read.metadata.ReadSheet;
import cn.idev.excel.metadata.Head;
import cn.idev.excel.context.AnalysisContext;
import cn.idev.excel.event.AnalysisEventListener;
import cn.idev.excel.read.metadata.holder.ReadSheetHolder;
import cn.idev.excel.read.metadata.holder.ReadWorkbookHolder;
import cn.idev.excel.metadata.property.ExcelContentProperty;
import cn.idev.excel.read.listener.ReadListener;
import cn.idev.excel.exception.ExcelDataConvertException;
import cn.idev.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import cn.iocoder.yudao.framework.common.util.http.HttpUtils;
import cn.iocoder.yudao.framework.excel.core.handler.ColumnWidthMatchStyleStrategy;
import cn.iocoder.yudao.framework.excel.core.handler.SelectSheetWriteHandler;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * Excel 工具类
 *
 * @author 芋道源码
 */
public class ExcelUtils {

    /**
     * 将列表以 Excel 响应给前端
     *
     * @param response  响应
     * @param filename  文件名
     * @param sheetName Excel sheet 名
     * @param head      Excel head 头
     * @param data      数据列表哦
     * @param <T>       泛型，保证 head 和 data 类型的一致性
     * @throws IOException 写入失败的情况
     */
    public static <T> void write(HttpServletResponse response, String filename, String sheetName,
                                 Class<T> head, List<T> data) throws IOException {
        // 输出 Excel
        FastExcelFactory.write(response.getOutputStream(), head)
                .registerWriteHandler(new ColumnWidthMatchStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
                .registerWriteHandler(new SelectSheetWriteHandler(head)) // 基于固定 sheet 实现下拉框
                .registerConverter(new LongStringConverter()) // 避免 Long 类型丢失精度
                .sheet(sheetName)
                .doWrite(data);
        // 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
        response.addHeader("Content-Disposition", "attachment;filename=" + HttpUtils.encodeUtf8(filename));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    }

    public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            // 添加调试信息
            System.out.println("Reading Excel file: " + file.getOriginalFilename());
            System.out.println("File size: " + file.getSize());
            
            // 使用FastExcelFactory读取，自动检测格式
            List<T> result = FastExcelFactory.read(inputStream, head, new AnalysisEventListener<T>() {
                @Override
                public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                    System.out.println("Excel表头Map (read): " + headMap);
                    // 打印每个表头的详细信息
                    for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
                        String header = entry.getValue();
                        System.out.println("  Column " + entry.getKey() + ": '" + header + "'");
                        if (header != null) {
                            // 打印字符数组和Unicode编码
                            System.out.println("    Length: " + header.length());
                            System.out.println("    Characters: " + java.util.Arrays.toString(header.toCharArray()));
                            System.out.println("    Unicode: ");
                            for (int i = 0; i < header.length(); i++) {
                                System.out.println("      [" + i + "] '" + header.charAt(i) + "' (U+" + String.format("%04X", (int)header.charAt(i)) + ")");
                            }
                        }
                    }
                }
                
                @Override
                public void invoke(T data, AnalysisContext context) {
                    // 不处理数据，只分析表头
                }
                
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    System.out.println("所有分析完成 (read)");
                }
            }).sheet().doReadSync();
            
            System.out.println("Read " + result.size() + " rows from Excel");
            
            // 打印读取到的数据
            for (int i = 0; i < Math.min(3, result.size()); i++) {
                System.out.println("Row " + (i+1) + ": " + result.get(i));
            }
            
            return result;
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (Exception ignored) {}
            }
        }
    }
    
    /**
     * 读取Excel文件的另一种方法
     */
    public static <T> List<T> readWithDebug(MultipartFile file, Class<T> head) throws IOException {
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            // 添加调试信息
            System.out.println("Reading Excel file (debug mode): " + file.getOriginalFilename());
            System.out.println("File size: " + file.getSize());
            
            // 使用FastExcelFactory读取，带更多调试信息，自动检测格式
            List<T> result = FastExcelFactory.read(inputStream, head, new AnalysisEventListener<T>() {
                @Override
                public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                    System.out.println("Excel表头Map (readWithDebug): " + headMap);
                    // 打印每个表头的详细信息
                    for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
                        String header = entry.getValue();
                        System.out.println("  Column " + entry.getKey() + ": '" + header + "'");
                        if (header != null) {
                            // 打印字符数组和Unicode编码
                            System.out.println("    Length: " + header.length());
                            System.out.println("    Characters: " + java.util.Arrays.toString(header.toCharArray()));
                            System.out.println("    Unicode: ");
                            for (int i = 0; i < header.length(); i++) {
                                System.out.println("      [" + i + "] '" + header.charAt(i) + "' (U+" + String.format("%04X", (int)header.charAt(i)) + ")");
                            }
                        }
                    }
                }
                
                @Override
                public void invoke(T data, AnalysisContext context) {
                    // 打印读取到的数据用于调试
                    System.out.println("读取到的数据行: " + data);
                }
                
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    System.out.println("所有分析完成 (readWithDebug)");
                }
            }).autoCloseStream(true).doReadAllSync();
            
            System.out.println("Read " + result.size() + " rows from Excel (debug mode)");
            
            // 打印读取到的数据
            for (int i = 0; i < Math.min(3, result.size()); i++) {
                System.out.println("Row " + (i+1) + " (debug): " + result.get(i));
            }
            
            return result;
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (Exception ignored) {}
            }
        }
    }
    
    /**
     * 读取Excel文件，支持多个工作表
     * 
     * @param file Excel文件
     * @param head 数据类
     * @param sheetNames 要读取的工作表名称列表，如果为null或空则读取所有工作表
     * @return 所有工作表数据的合并列表
     */
    public static <T> List<T> readMultipleSheets(MultipartFile file, Class<T> head, List<String> sheetNames) throws IOException {
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            System.out.println("Reading Excel file with multiple sheets: " + file.getOriginalFilename());
            System.out.println("File size: " + file.getSize());
            
            // 用于存储所有工作表的数据
            List<T> allData = new ArrayList<>();
            
            // 创建读取监听器
            AnalysisEventListener<T> listener = new AnalysisEventListener<T>() {
                private List<T> currentSheetData = new ArrayList<>();
                
                @Override
                public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                    System.out.println("Excel表头Map (readMultipleSheets): " + headMap);
                    // 打印每个表头的详细信息
                    for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
                        String header = entry.getValue();
                        System.out.println("  Column " + entry.getKey() + ": '" + header + "'");
                    }
                }
                
                @Override
                public void invoke(T data, AnalysisContext context) {
                    // 收集当前工作表的数据
                    currentSheetData.add(data);
                    System.out.println("读取到的数据行 (multiple sheets): " + data);
                }
                
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    System.out.println("工作表 '" + context.readSheetHolder().getSheetName() + "' 分析完成，共读取 " + currentSheetData.size() + " 行数据");
                    // 将当前工作表的数据添加到总数据中
                    allData.addAll(currentSheetData);
                    // 清空当前工作表数据，为下一个工作表做准备
                    currentSheetData.clear();
                }
            };
            
            // 构建读取器
            ExcelReaderBuilder readerBuilder = FastExcelFactory.read(inputStream, head, listener);
            
            // 读取所有工作表
            readerBuilder.doReadAll();
            
            System.out.println("总共读取 " + allData.size() + " 行数据 from all sheets");
            
            // 打印读取到的数据
            for (int i = 0; i < Math.min(3, allData.size()); i++) {
                System.out.println("Row " + (i+1) + " (multiple sheets): " + allData.get(i));
            }
            
            return allData;
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (Exception ignored) {}
            }
        }
    }
    
    /**
     * 读取Excel文件，基于列位置而不是表头名称
     */
    public static <T> List<T> readByPosition(MultipartFile file, Class<T> head) throws IOException {
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            // 添加调试信息
            System.out.println("Reading Excel file by position: " + file.getOriginalFilename());
            System.out.println("File size: " + file.getSize());
            
            // 使用FastExcelFactory读取，基于列位置
            List<T> result = FastExcelFactory.read(inputStream, head, new AnalysisEventListener<T>() {
                @Override
                public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                    System.out.println("Excel表头Map (readByPosition): " + headMap);
                    // 打印每个表头的详细信息
                    for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
                        String header = entry.getValue();
                        System.out.println("  Column " + entry.getKey() + ": '" + header + "'");
                    }
                }
                
                @Override
                public void invoke(T data, AnalysisContext context) {
                    // 打印读取到的数据用于调试
                    System.out.println("读取到的数据行 (by position): " + data);
                }
                
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    System.out.println("所有分析完成 (readByPosition)");
                }
            }).sheet().doReadSync();
            
            System.out.println("Read " + result.size() + " rows from Excel by position");
            
            // 打印读取到的数据
            for (int i = 0; i < Math.min(3, result.size()); i++) {
                System.out.println("Row " + (i+1) + " (by position): " + result.get(i));
            }
            
            return result;
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (Exception ignored) {}
            }
        }
    }
    
    /**
     * 读取Excel文件，基于列位置手动映射数据
     * 针对复杂Excel进行优化处理
     */
    public static <T> List<T> readByPositionManual(MultipartFile file, Class<T> head) throws IOException {
        return readByPositionManualMultipleSheets(file, head, null);
    }
    
    /**
     * 读取Excel文件，基于列位置手动映射数据，支持多个工作表
     * 针对复杂Excel进行优化处理
     * 
     * @param file Excel文件
     * @param head 数据类
     * @param sheetNames 要读取的工作表名称列表，如果为null或空则读取所有工作表
     * @return 所有工作表数据的合并列表
     */
    public static <T> List<T> readByPositionManualMultipleSheets(MultipartFile file, Class<T> head, List<String> sheetNames) throws IOException {
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            // 添加调试信息
            System.out.println("Reading Excel file by position (manual, multiple sheets): " + file.getOriginalFilename());
            System.out.println("File size: " + file.getSize());
            
            // 用于存储所有工作表的原始数据
            List<Map<Integer, String>> allRawDataList = new ArrayList<>();
            
            // 存储表头信息
            final Map<Integer, String> headerMap = new HashMap<>();
            
            // 创建读取监听器
            AnalysisEventListener<Map<Integer, String>> listener = new AnalysisEventListener<Map<Integer, String>>() {
                @Override
                public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                    System.out.println("Excel表头Map (readByPositionManualMultipleSheets): " + headMap);
                    // 存储表头信息（只存储第一个工作表的表头）
                    if (headerMap.isEmpty()) {
                        headerMap.putAll(headMap);
                    }
                    
                    // 打印每个表头的详细信息
                    for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
                        String header = entry.getValue();
                        System.out.println("  Column " + entry.getKey() + ": '" + header + "'");
                        if (header != null) {
                            // 打印字符数组和Unicode编码
                            System.out.println("    Length: " + header.length());
                            System.out.println("    Characters: " + java.util.Arrays.toString(header.toCharArray()));
                        }
                    }
                }
                
                @Override
                public void invoke(Map<Integer, String> data, AnalysisContext context) {
                    // 打印读取到的原始数据用于调试
                    System.out.println("读取到的原始数据行 (multiple sheets): " + data);
                    
                    // 检查数据中是否有内容
                    boolean hasContent = false;
                    for (String value : data.values()) {
                        if (value != null && !value.trim().isEmpty()) {
                            hasContent = true;
                            break;
                        }
                    }
                    
                    // 只保存有内容的行
                    if (hasContent) {
                        allRawDataList.add(new HashMap<>(data)); // 创建副本避免引用问题
                    }
                }
                
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    System.out.println("工作表 '" + context.readSheetHolder().getSheetName() + "' 分析完成");
                }
            };
            
            // 构建读取器
            ExcelReaderBuilder readerBuilder = FastExcelFactory.read(inputStream, listener);
            
            // 读取所有工作表
            readerBuilder.doReadAll();
            
            System.out.println("Read " + allRawDataList.size() + " rows from Excel by position (manual, multiple sheets)");
            
            // 打印表头信息
            System.out.println("表头信息：");
            for (Map.Entry<Integer, String> entry : headerMap.entrySet()) {
                System.out.println("  列 " + entry.getKey() + ": '" + entry.getValue() + "'");
            }
            
            // 手动将原始数据映射到目标对象
            List<T> result = new ArrayList<>();
            try {
                for (Map<Integer, String> rawData : allRawDataList) {
                    T obj = head.getDeclaredConstructor().newInstance();
                    
                    // 使用反射设置字段值
                    java.lang.reflect.Field[] fields = head.getDeclaredFields();
                    System.out.println("开始映射对象字段，原始数据: " + rawData);
                    
                    // 打印所有原始数据的键值
                    System.out.println("原始数据所有键: " + rawData.keySet());
                    
                    for (java.lang.reflect.Field field : fields) {
                        ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                        if (excelProperty != null && excelProperty.index() >= 0) {
                            int index = excelProperty.index();
                            String value = rawData.get(index);
                            
                            System.out.println("字段: " + field.getName() + ", 索引: " + index + 
                                ", 表头: " + headerMap.get(index) + ", 原始值: " + value);
                            
                            try {
                                field.setAccessible(true);
                                
                                // 处理值为null的情况
                                if (value == null) {
                                    // 检查是否有可能是因为索引问题
                                    if (!rawData.containsKey(index)) {
                                        System.out.println("  警告：索引 " + index + " 在原始数据中不存在");
                                        
                                        // 尝试查找是否有其他可能的索引
                                        String fieldName = field.getName();
                                        for (Map.Entry<Integer, String> entry : headerMap.entrySet()) {
                                            if (entry.getValue() != null && fieldNameMatches(fieldName, entry.getValue())) {
                                                System.out.println("  可能的匹配: 索引 " + entry.getKey() + 
                                                    " ('" + entry.getValue() + "'), 值: " + rawData.get(entry.getKey()));
                                            }
                                        }
                                    }
                                    
                                    // 为null时设置为空字符串，避免NPE
                                    if (field.getType() == String.class) {
                                        field.set(obj, "");
                                        System.out.println("  设置空字符串");
                                    }
                                } else {
                                    // 简单类型转换
                                    if (field.getType() == String.class) {
                                        // 处理文本中的特殊字符
                                        value = value.trim();
                                        field.set(obj, value);
                                        System.out.println("  设置字段值成功: " + field.getName() + " = " + value);
                                    }
                                    // 可以添加其他类型转换逻辑
                                }
                            } catch (Exception e) {
                                System.out.println("设置字段值失败: " + field.getName() + ", 值: " + value + ", 错误: " + e.getMessage());
                                e.printStackTrace();
                            }
                        }
                    }
                    result.add(obj);
                    System.out.println("映射完成的对象: " + obj);
                }
            } catch (Exception e) {
                System.out.println("创建对象实例失败: " + e.getMessage());
                e.printStackTrace();
            }
            
            // 打印转换后的数据
            for (int i = 0; i < Math.min(3, result.size()); i++) {
                System.out.println("Row " + (i+1) + " (manual mapping, multiple sheets): " + result.get(i));
            }
            
            return result;
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (Exception ignored) {}
            }
        }
    }
    
    /**
     * 判断字段名称是否与表头匹配
     */
    private static boolean fieldNameMatches(String fieldName, String headerText) {
        if (headerText == null) return false;
        
        // 将驼峰命名转换为中文可能的名称
        String simplifiedFieldName = fieldName
            .replaceAll("([a-z])([A-Z])", "$1_$2")
            .toLowerCase();
        
        // 特殊字段映射
        Map<String, String[]> fieldMappings = new HashMap<>();
        fieldMappings.put("number", new String[]{"序号", "编号", "序列"});
        fieldMappings.put("category_code", new String[]{"大类编码", "类别编码"});
        fieldMappings.put("category_name", new String[]{"大类名称", "类别名称"});
        fieldMappings.put("code", new String[]{"专业编码", "编码"});
        fieldMappings.put("skill_level", new String[]{"等级", "技能等级", "级别"});
        fieldMappings.put("sub_category_name", new String[]{"专业名称", "名称"});
        fieldMappings.put("training_goal", new String[]{"培养目标", "培训目标"});
        fieldMappings.put("study_year", new String[]{"学习年限", "年限"});
        fieldMappings.put("occupational_ability", new String[]{"职业能力", "能力"});
        fieldMappings.put("corresponding_occupation", new String[]{"对应或相关职业", "对应职业", "工种", "职业"});
        fieldMappings.put("vocational_qualification", new String[]{"职业资格", "职业技能等级", "资格"});
        fieldMappings.put("major_teaching_content", new String[]{"专业主要教学内容", "教学内容", "内容"});
        fieldMappings.put("major_direction", new String[]{"专业方向", "方向"});
        fieldMappings.put("upper_major_code", new String[]{"对应上一级专业编码", "上级编码", "上一级编码"});
        fieldMappings.put("next_level_code", new String[]{"对应下一级专业编码", "下级编码", "下一级编码"});
        
        // 检查是否在映射中
        if (fieldMappings.containsKey(simplifiedFieldName)) {
            String[] possibleNames = fieldMappings.get(simplifiedFieldName);
            for (String name : possibleNames) {
                if (headerText.contains(name)) {
                    return true;
                }
            }
        }
        
        // 简单包含检查
        return headerText.contains(fieldName) || 
               headerText.contains(simplifiedFieldName) || 
               simplifiedFieldName.contains(headerText.toLowerCase());
    }
}